package com.example.demo;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.*;

/**
 * ClassName: OrderDAO
 * Package: com.example.demo
 * Description: @Version 1.0
 */
public class OrderDAO {
    public static void main(String[] args) {
//        selectSales();
//        minAndMax();
    }
    public static Map<Integer,List<Long>> selectSales(){
        Connection connection=null;
        PreparedStatement ps=null;
        ResultSet rs=null;
        Map<Integer,List<Long>> map=new HashMap<>();
        try {
            connection=JDBCUtil.getConnection();
            ps=connection.prepareStatement("SELECT\n" +
                    "\t(\n" +
                    "\tSELECT\n" +
                    "\t\tORDERDATE orders \n" +
                    "\tGROUP BY\n" +
                    "\t\tYEAR ( ORDERDATE ),MONTH ( ORDERDATE )) as 'year_month',\n" +
                    "\tFLOOR(sum( CAST(TOTALPRICE as DECIMAL(20,2)))) as 'month_money'\n" +
                    "FROM\n" +
                    "\torders \n" +
                    "GROUP BY\n" +
                    "\tYEAR ( ORDERDATE ),\n" +
                    "\tMONTH (\n" +
                    "\tORDERDATE \n" +
                    "\t)");
            rs=ps.executeQuery();
            int year=0;
            int month=0;
            long money=0;
            List<Long> list=null;
            while (rs.next()){
                String[] str=rs.getString("year_month").split("-");
                year=Integer.parseInt(str[0]);
                month=Integer.parseInt(str[1]);
                money=rs.getLong("month_money");
//                如果map中不存在该年份，则新建一个list
                if(!map.containsKey(year)){
//                if(month==1){
                    list=new ArrayList<>();
                    for (int i=0;i<12;i++){
                        list.add(0L);
                    }
                }
//                不管是几月，都应该将money付给对应月份
                list.set(month-1,money);
//                System.out.println(list);
//                每次都将map进行覆盖，获取最新数据
                map.put(year,list);
            }

//            for (Map.Entry<Integer, List<String>> entry:map.entrySet()) {
//                System.out.println(entry.getKey()+"-->"+entry.getValue());
//            }

        } catch (SQLException | ClassNotFoundException e) {
            e.printStackTrace();
        }finally {
            JDBCUtil.close(rs);
            JDBCUtil.close(ps);
            JDBCUtil.close(connection);
        }
        return map;
    }

    public static Map<String,List<Integer>> minAndMax(){
        Connection connection=null;
        PreparedStatement ps=null;
        ResultSet rs=null;
        Map<String,List<Integer>> map=new HashMap<>();
        List<Integer> list=null;
        try {
            connection=JDBCUtil.getConnection();
            ps=connection.prepareStatement("select FLOOR(MIN(CAST(TOTALPRICE as DECIMAL(20,2)))) as 'min_money',\n" +
                    "FLOOR(MAX(CAST(TOTALPRICE as DECIMAL(20,2)))) as 'max_money'\n" +
                    "from orders");
            rs=ps.executeQuery();
            int min=0;
            int max=0;
            while(rs.next()){
                list=new ArrayList<>();
                min=rs.getInt("min_money");
                list.add(min);
                map.put("min",list);
                list=new ArrayList<>();
                max=rs.getInt("max_money");
                list.add(max);
                map.put("max",list);
            }
            list=new ArrayList<>();
            for (int i=1;i<=12;i++){
                list.add(i);
            }
            map.put("integerList",list);
            System.out.println(map);
        } catch (SQLException | ClassNotFoundException e) {
            e.printStackTrace();
        }finally {
            JDBCUtil.close(rs);
            JDBCUtil.close(ps);
            JDBCUtil.close(connection);
        }
        return map;
    }
}
